package cn.com.css.taxInspection.workflow.controller;

import cn.com.css.taxInspection.attention.entity.CljamxEntity;
import cn.com.css.taxInspection.attention.entity.QtjamxEntity;
import cn.com.css.taxInspection.attention.entity.YljamxEntity;
import cn.com.css.taxInspection.attention.service.CljamxService;
import cn.com.css.taxInspection.attention.service.DqhbService;
import cn.com.css.taxInspection.attention.service.QtjamxService;
import cn.com.css.taxInspection.attention.service.YljamxService;
import cn.com.css.taxInspection.common.result.Result;
import cn.com.css.taxInspection.gt3.entity.JcSrbcEntity;
import cn.com.css.taxInspection.gt3.entity.ZxhjBlxkEntity;
import cn.com.css.taxInspection.gt3.entity.ZxhjQqtjEntity;
import cn.com.css.taxInspection.gt3.mapper.ZxhjBlxkMapper;
import cn.com.css.taxInspection.gt3.mapper.ZxhjQqtjMapper;
import cn.com.css.taxInspection.gt3.service.JcSrbcService;
import cn.com.css.taxInspection.gt3.service.ZxhjBlxkService;
import cn.com.css.taxInspection.gt3.service.ZxhjQqtjService;
import com.baomidou.dynamic.datasource.annotation.DS;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import com.baomidou.mybatisplus.core.metadata.IPage;
import cn.com.css.taxInspection.common.aop.annotation.LogAnnotation;
import cn.com.css.taxInspection.common.constants.Constants;
import cn.com.css.taxInspection.common.result.DataResult;
import cn.com.css.taxInspection.gt3.entity.JcAjxxEntity;
import cn.com.css.taxInspection.gt3.service.JcAjxxService;
import cn.com.css.taxInspection.system.service.impl.HttpSessionService;
import cn.com.css.taxInspection.util.TsBqUtils;
import cn.com.css.taxInspection.workflow.entity.WorkflowCompensateEntity;
import cn.com.css.taxInspection.workflow.service.WorkflowCompensateService;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;


import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

import java.io.InputStream;
import java.util.concurrent.TimeUnit;
import java.util.regex.Pattern;

@Slf4j
@RestController
@RequestMapping("/compensate")
public class WorkflowCompensateController {

    @Resource
    private JcAjxxService jcAjxxService;
    @Resource
    private WorkflowCompensateService workflowCompensateService;
    @Resource
    private HttpSessionService httpSessionService;
    @Resource
    private JcSrbcService jcSrbcService;
    @Resource
    private YljamxService yljamxService;
    @Resource
    private QtjamxService qtjamxService;
    @Resource
    private CljamxService cljamxService;
    @Resource
    private DqhbService dqhbService;

    @Resource
    private ZxhjBlxkService zxhjBlxkService;

    @Resource
    private ZxhjQqtjService zxhjQqtjService;

    @Resource
    private ZxhjBlxkMapper zxhjBlxkMapper;

    @Resource
    private ZxhjQqtjMapper zxhjQqtjMapper;

    @ApiOperation(value = "")
    @PostMapping("/list1")
    @ResponseBody
    public DataResult list1(@RequestBody JcAjxxEntity jcAjxxEntity) {
        String jcajbh = jcAjxxEntity.getJcajbh();
        if (TsBqUtils.isNull(jcajbh)) {
            return DataResult.fail("请输入稽查案卷编号！");
        }
        IPage<JcAjxxEntity> iPage = jcAjxxService.pageInfo(jcAjxxEntity);
        List<JcAjxxEntity> records = iPage.getRecords();
        for (JcAjxxEntity jcAjxx : records) {
            String jcajxxuuid = jcAjxx.getJcajxxuuid();
            WorkflowCompensateEntity compensate = workflowCompensateService.getById(jcajxxuuid);
            if (TsBqUtils.isNotNull(compensate)) {
                jcAjxx.setAjdyxx(compensate.getAjdyxx());
                jcAjxx.setIsSqgalhba(compensate.getIsSqgalhba());
                jcAjxx.setIsDya(compensate.getIsDya());
                jcAjxx.setIsHmd(compensate.getIsHmd());
                jcAjxx.setIsYasc(compensate.getIsYasc());
                jcAjxx.setIsYsgajg(compensate.getIsYsgajg());
                jcAjxx.setQzcs(compensate.getQzcs());
                jcAjxx.setCgzzqyjwjskje(compensate.getCgzzqyjwjskje());
                jcAjxx.setAjdx(compensate.getAjdx());
                jcAjxx.setUpdateTime(compensate.getUpdateTime());
                jcAjxx.setUpdateId(compensate.getUpdateId());
                jcAjxx.setYcsdyqyksjsjtz(compensate.getYcsdyqyksjsjtz());
                jcAjxx.setAjccsxjfother(compensate.getAjccsxjfother());
                jcAjxx.setSsjdssj(compensate.getSsjdssj());
                jcAjxx.setQtjf(compensate.getQtjf());
                jcAjxx.setAjcccgjyzfxctjjf(compensate.getAjcccgjyzfxctjjf());
                jcAjxx.setDxaldjyzfjf(compensate.getDxaldjyzfjf());
                jcAjxx.setDjjfsrxxgcddesdjs(compensate.getDjjfsrxxgcddesdjs());
                jcAjxx.setDjjfcjjczzdjppxsgz(compensate.getDjjfcjjczzdjppxsgz());
                jcAjxx.setJjjf(compensate.getJjjf());
            }
        }
        return DataResult.success(iPage);
    }

    @ApiOperation(value = "补偿业务-收入补偿")
    @LogAnnotation(title = "补偿业务查询", action = "收入补偿")
    @PostMapping("/querySrbc")
    @ResponseBody
    public DataResult querySrbc(@RequestBody JcSrbcEntity jcSrbcEntity) throws ParseException {
        if (TsBqUtils.isNull(jcSrbcEntity)) {
            return DataResult.fail("暂无数据！");
        }
        IPage<JcSrbcEntity> jcSrbcEntityIPage = jcSrbcService.querySrbcList(jcSrbcEntity);
        return DataResult.success(jcSrbcEntityIPage);
    }

    @ApiOperation(value = "补偿业务-收入补偿")
    @LogAnnotation(title = "补偿业务导出", action = "导出收入补偿")
    @PostMapping("/exportSrbc")
    @ResponseBody
    public DataResult exportSrbc(@RequestBody JcSrbcEntity jcSrbcEntity) throws ParseException {
        if (TsBqUtils.isNull(jcSrbcEntity)) {
            return DataResult.fail("暂无数据！");
        }
        List<JcSrbcEntity> jcSrbcEntityList = jcSrbcService.exportSrbcList(jcSrbcEntity);
        return DataResult.success(jcSrbcEntityList);
    }


    @ApiOperation(value = "补偿业务-收入补偿")
    @LogAnnotation(title = "补偿业务更新", action = "更新操作")
    @PostMapping("/updateSrbc")
    @ResponseBody
    @DS("oracle")
    public DataResult updateSrbc(@RequestBody JcSrbcEntity jcSrbcEntity) {
        if (TsBqUtils.isNull(jcSrbcEntity)) {
            return DataResult.fail("请选择正确的数据！");
        }
        Boolean result = jcSrbcService.updateById(jcSrbcEntity);
        return DataResult.success(result);
    }

    @ApiOperation(value = "补偿业务-收入补偿")
    @LogAnnotation(title = "补偿业务删除", action = "删除操作")
    @PostMapping("/deleteSrbc")
    @ResponseBody
    @DS("oracle")
    public DataResult deleteSrbc(@RequestBody JcSrbcEntity jcSrbcEntity) {
        if (TsBqUtils.isNull(jcSrbcEntity)) {
            return DataResult.fail("请选择正确的数据！");
        }
        Boolean result = jcSrbcService.removeById(jcSrbcEntity);
        return DataResult.success(result);
    }

    @ApiOperation(value = "")
    @PostMapping("/update1")
    @ResponseBody
    public DataResult update1(@RequestBody WorkflowCompensateEntity workflowCompensateEntity) {
        String currentDeptId = httpSessionService.getCurrentDeptId();
        String currentUserId = httpSessionService.getCurrentUserId();
        Date date = new Date();
        workflowCompensateEntity.setUpdateId(currentUserId);
        workflowCompensateEntity.setTaxorgcode(currentDeptId);
        workflowCompensateEntity.setUpdateTime(date);
        workflowCompensateEntity.setDeleted(Constants.DATA_NOT_DELETED);
        workflowCompensateService.saveOrUpdate(workflowCompensateEntity);
        return DataResult.success();
    }

    @PostMapping("/uploadExcel")
    @DS("oracle")
    public ResponseEntity<?> uploadExcel(@RequestParam("file") MultipartFile file) throws IOException {
        if (file == null || file.isEmpty()) {
            return ResponseEntity.ok(Result.error("上传的文件为空！"));
        }

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null || sheet.getPhysicalNumberOfRows() <= 1) {
            workbook.close();
            return ResponseEntity.ok(Result.error("Excel 内容为空，至少需要包含一行数据！"));
        }

        int validDataCount = 0;
        List<JcSrbcEntity> records = new ArrayList<>();

        for (Row row : sheet) {
            if (row.getRowNum() == 0 || isRowEmpty(row)) continue;
            JcSrbcEntity jcSrbcEntity = new JcSrbcEntity();
            jcSrbcEntity.setUuid(TsBqUtils.getUUID());
            jcSrbcEntity.setAjbh(getStringCellValue(row.getCell(0)));
            jcSrbcEntity.setAjmc(getStringCellValue(row.getCell(1)));
            jcSrbcEntity.setNsrmc(getStringCellValue(row.getCell(2)));
            jcSrbcEntity.setNsrsbh(getStringCellValue(row.getCell(3)));
            jcSrbcEntity.setZsxm(getStringCellValue(row.getCell(4)));
            jcSrbcEntity.setZspm(getStringCellValue(row.getCell(5)));
            jcSrbcEntity.setSjje(getDoubleCellValue(row.getCell(6)));
            jcSrbcEntity.setRkrq(getDateCellValue(row.getCell(7)));
            jcSrbcEntity.setRkxhrq(getDateCellValue(row.getCell(8)));
            jcSrbcEntity.setSkssswjg(getStringCellValue(row.getCell(9)));
            jcSrbcEntity.setSkzl(getStringCellValue(row.getCell(10)));
            jcSrbcEntity.setSksx(getStringCellValue(row.getCell(11)));
            jcSrbcEntity.setZyj(getDoubleCellValue(row.getCell(12)));
            jcSrbcEntity.setLrrq(new Date());
            jcSrbcService.save(jcSrbcEntity);

            records.add(jcSrbcEntity);
            validDataCount++;
        }

        workbook.close();

        if (validDataCount == 0) {
            return ResponseEntity.ok(Result.error("Excel 中没有有效的数据行！"));
        }

        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("records", records);

        return ResponseEntity.ok(Result.success("共导入 " + validDataCount + " 条记录", dataMap));
    }

    @PostMapping("/uploadYljamxExcel")
    @DS("oracle")
    public ResponseEntity<?> uploadYljamxExcel(@RequestParam("file") MultipartFile file) throws IOException {
        if (file == null || file.isEmpty() || file.getOriginalFilename() == null) {
            return ResponseEntity.ok(Result.error("上传的文件为空！"));
        }
        System.out.println("上传文件名：" + file.getOriginalFilename());

        // 匹配："一类积案清理明细表模板.xlsx"及其带(1)、(2)等版本
        Pattern pattern = Pattern.compile("^一类积案明细表模板( ?\\(\\d+\\))?\\.xlsx$");
        if (!pattern.matcher(Objects.requireNonNull(file.getOriginalFilename())).matches()) {
            return ResponseEntity.ok(Result.error("请使用一类积案清理明细表模板上传！"));
        }

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null || sheet.getPhysicalNumberOfRows() <= 1) {
            workbook.close();
            return ResponseEntity.ok(Result.error("Excel 内容为空，至少需要包含一行数据！"));
        }

        int validDataCount = 0;
        List<YljamxEntity> records = new ArrayList<>();

        for (Row row : sheet) {
            if (row.getRowNum() < 3 || isRowEmpty(row)) continue;

            YljamxEntity yljamxEntity = new YljamxEntity();
            yljamxEntity.setUuid(TsBqUtils.getUUID());
            yljamxEntity.setJcajbh(getStringCellValue(row.getCell(0)));
            yljamxEntity.setNsrsbh(getStringCellValue(row.getCell(1)));
            yljamxEntity.setNsrmc(getStringCellValue(row.getCell(2)));
            yljamxEntity.setLarq(getDateCellValue(row.getCell(3)));
            yljamxEntity.setHylx(getStringCellValue(row.getCell(4)));
            yljamxEntity.setSzhj(getStringCellValue(row.getCell(5)));
            yljamxEntity.setAjlx(getStringCellValue(row.getCell(6)));
            yljamxEntity.setJayy(getStringCellValue(row.getCell(7)));
            yljamxEntity.setSfql(getStringCellValue(row.getCell(8)));
            yljamxEntity.setClyj(getStringCellValue(row.getCell(9)));
            yljamxEntity.setJaszhj(getStringCellValue(row.getCell(10)));
            yljamxEntity.setSrsk(getStringCellValue(row.getCell(11)));
            yljamxEntity.setSrznj(getStringCellValue(row.getCell(12)));
            yljamxEntity.setSrfk(getStringCellValue(row.getCell(13)));
            yljamxEntity.setSrxj(getStringCellValue(row.getCell(14)));
            yljamxEntity.setRksk(getStringCellValue(row.getCell(15)));
            yljamxEntity.setRkznj(getStringCellValue(row.getCell(16)));
            yljamxEntity.setRkfk(getStringCellValue(row.getCell(17)));
            yljamxEntity.setRkxj(getStringCellValue(row.getCell(18)));
            yljamxEntity.setDqzt(getStringCellValue(row.getCell(19)));
            yljamxEntity.setSzdw(getStringCellValue(row.getCell(20)));
            yljamxService.save(yljamxEntity);

            records.add(yljamxEntity);
            validDataCount++;
        }

        workbook.close();

        if (validDataCount == 0) {
            return ResponseEntity.ok(Result.error("Excel 中没有有效的数据行！"));
        }

        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("records", records);

        return ResponseEntity.ok(Result.success("共导入 " + validDataCount + " 条记录", dataMap));
    }

    private boolean isRowEmpty(Row row) {
        if (row == null) return true;
        for (int c = 0; c <= row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK) {
                if (cell.getCellType() == CellType.STRING && !cell.getStringCellValue().trim().isEmpty()) return false;
                if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.BOOLEAN) return false;
            }
        }
        return true;
    }


    // 工具方法：读取字符串单元格
    private String getStringCellValue(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                // 判断是否为整数
                double numValue = cell.getNumericCellValue();
                if (numValue == (long)numValue) {
                    return String.valueOf((long)numValue);  // 整数返回无小数点
                } else {
                    return String.valueOf(numValue);  // 小数返回原样
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (IllegalStateException e) {
                    // 公式计算结果为数字时
                    double formulaNumValue = cell.getNumericCellValue();
                    if (formulaNumValue == (long)formulaNumValue) {
                        return String.valueOf((long)formulaNumValue);
                    } else {
                        return String.valueOf(formulaNumValue);
                    }
                }
            case BLANK:
            case ERROR:
            default:
                return "";
        }
    }

    // 工具方法：读取数字单元格
    private Double getDoubleCellValue(Cell cell) {
        if (cell == null) return null;
        try {
            if (cell.getCellType() == CellType.NUMERIC) {
                return cell.getNumericCellValue();
            } else if (cell.getCellType() == CellType.STRING) {
                String val = cell.getStringCellValue().trim();
                if (!val.isEmpty()) {
                    return Double.parseDouble(val);
                }
            }
        } catch (Exception e) {
            log.debug("getDoubleCellValue error:{}", e.getMessage());
        }
        return null;
    }


    // 工具方法：读取日期单元格
    private Date getDateCellValue(Cell cell) {
        if (cell == null) return null;
        try {
            if (cell.getCellType() == CellType.NUMERIC && DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else if (cell.getCellType() == CellType.STRING) {
                String dateStr = cell.getStringCellValue().trim();
                if (dateStr.isEmpty()) return null;

                // 支持多种常见格式
                List<String> patterns = Arrays.asList("yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd", "yyyy/MM/dd HH:mm:ss", "yyyy/MM/dd");
                for (String pattern : patterns) {
                    try {
                        return new SimpleDateFormat(pattern).parse(dateStr);
                    } catch (Exception ignored) {
                    }
                }
            }
        } catch (Exception ex) {
            log.debug("getDateCellValue error:{}", ex.getMessage());
        }
        return null;
    }


    /**
     * 下载Excel模板（兼容Java 8）  两种写法
     *  HttpServletResponse
     *  第一种
     *  方案	优势	适用场景
     *  ResponseEntity<Resource>	代码最简洁，Spring 自动处理资源关闭	   中小文件（<100MB）
     *  StreamingResponseBody	    完全流式传输，零内存压力	               大文件（如视频、GB级数据）
     *  原始 HttpServletResponse	    最底层控制，兼容性最强	               需要精细控制响应时
     */
    //    @GetMapping("/downloadTemplate")
    //    public void downloadTemplate(HttpServletResponse response) throws IOException {
    //        try {
    //            String filePath = "templates/compensate/收入补偿模板.xlsx";
    //            InputStream inputStream = Thread.currentThread()
    //                    .getContextClassLoader()
    //                    .getResourceAsStream(filePath);
    //
    //            if (inputStream == null) {
    //                response.setStatus(HttpServletResponse.SC_NOT_FOUND);
    //                return;
    //            }
    //
    //            String encodedFileName = URLEncoder.encode("收入补偿模板.xlsx", StandardCharsets.UTF_8.name());
    //            response.setContentType("application/octet-stream");
    //            response.setHeader("Content-Disposition", "attachment; filename*=UTF-8''" + encodedFileName);
    //
    //            ServletOutputStream outputStream = response.getOutputStream();
    //            byte[] buffer = new byte[8192];
    //            int bytesRead;
    //            while ((bytesRead = inputStream.read(buffer)) != -1) {
    //                outputStream.write(buffer, 0, bytesRead);
    //            }
    //            inputStream.close();
    //            outputStream.flush();
    //        } catch (Exception e) {
    //            response.setStatus(HttpServletResponse.SC_INTERNAL_SERVER_ERROR);
    //            log.error("收入补偿文件下载失败:", e);
    //        }
    //
    //    }

    /**
     * 下载Excel模板（兼容Java 8）  两种写法
     * StreamingResponseBody
     * 第二种
     */
    @GetMapping("/downloadTemplate")
    public ResponseEntity<StreamingResponseBody> downloadTemplateStream() {
        String filePath = "templates/compensate/收入补偿模板.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=收入补偿模板.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }


    @GetMapping("/downloadDqhb_mb")
    public ResponseEntity<StreamingResponseBody> downloadDqhb_mb() {
        String filePath = "templates/attention/定期汇报.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=定期汇报.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }


    @GetMapping("/downloadYljamx_mb")
    public ResponseEntity<StreamingResponseBody> downloadYljamx_mb() {
        String filePath = "templates/excel/一类积案情理明细表模板.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=一类积案情理明细表模板.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }

    @GetMapping("/downloadQtjamx_mb")
    public ResponseEntity<StreamingResponseBody> downloadQtjamx_mb() {
        String filePath = "templates/excel/其他长期未结案件清理明细表.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=其他长期未结案件清理明细表.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }

    @GetMapping("/downloadCljamx_mb")
    public ResponseEntity<StreamingResponseBody> downloadCljamx_mb() {
        String filePath = "templates/excel/立案超6个月未结案件清理明细表模板.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=立案超6个月未结案件清理明细表模板.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }


    // 暴力虚开 上传
    @PostMapping("/uploadZxhjBlxkExcel")
    @DS("oracle")
    public ResponseEntity<?> uploadZxhjBlxkExcel(@RequestParam("file") MultipartFile file) throws IOException {

        if (file == null || file.isEmpty() || file.getOriginalFilename() == null) {
            return ResponseEntity.ok(Result.error("上传的文件为空！"));
        }

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null || sheet.getPhysicalNumberOfRows() <= 1) {
            workbook.close();
            return ResponseEntity.ok(Result.error("Excel 内容为空，至少需要包含一行数据！"));
        }

        int validDataCount = 0;
        List<ZxhjBlxkEntity> records = new ArrayList<>();

        for (Row row : sheet) {
            if (row.getRowNum() < 2 || isRowEmpty(row)) continue;
            ZxhjBlxkEntity zxhjBlxkEntity = new ZxhjBlxkEntity();
            String yf = getStringCellValue(row.getCell(0));
            String swjgmc = getStringCellValue(row.getCell(1));
            ZxhjBlxkEntity existingEntity = zxhjBlxkMapper.findBlxkByYfAndSwjgmc(yf,swjgmc);
            if(TsBqUtils.isNotNull(existingEntity)){
                zxhjBlxkMapper.delByUuid(existingEntity.getUuid());
            }
            zxhjBlxkEntity.setUuid(TsBqUtils.getUUID());
            zxhjBlxkEntity.setYf(yf);
            zxhjBlxkEntity.setSwjgmc(String.valueOf(row.getCell(1)));
            zxhjBlxkEntity.setJcajbh(String.valueOf(row.getCell(2)));
            zxhjBlxkEntity.setQsze(getBigDecimal(row.getCell(3)));
            zxhjBlxkEntity.setByqqje(getBigDecimal(row.getCell(4)));
            zxhjBlxkMapper.insert(zxhjBlxkEntity);
            records.add(zxhjBlxkEntity);
            validDataCount++;
        }

        workbook.close();

        if (validDataCount == 0) {
            return ResponseEntity.ok(Result.error("Excel 中没有有效的数据行！"));
        }

        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("records", records);

        return ResponseEntity.ok(Result.success("共导入 " + validDataCount + " 条记录", dataMap));
    }

    // 清欠统计 上传
    @PostMapping("/uploadZxhjQqtjExcel")
    @DS("oracle")
    public ResponseEntity<?> uploadZxhjQqtjExcel(@RequestParam("file") MultipartFile file) throws IOException {

        if (file == null || file.isEmpty() || file.getOriginalFilename() == null) {
            return ResponseEntity.ok(Result.error("上传的文件为空！"));
        }

        Workbook workbook = new XSSFWorkbook(file.getInputStream());
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet == null || sheet.getPhysicalNumberOfRows() <= 1) {
            workbook.close();
            return ResponseEntity.ok(Result.error("Excel 内容为空，至少需要包含一行数据！"));
        }

        int validDataCount = 0;
        List<ZxhjQqtjEntity> records = new ArrayList<>();

        for (Row row : sheet) {
            if (row.getRowNum() < 6 || isRowEmpty(row)) continue;
            ZxhjQqtjEntity zxhjQqtjEntity = new ZxhjQqtjEntity();
            String yf = getStringCellValue(row.getCell(0));
            String swjgmc= getStringCellValue(row.getCell(1));
            ZxhjQqtjEntity existingEntity = zxhjQqtjMapper.findByYfAndSwjgmc(yf,swjgmc);
            if(TsBqUtils.isNotNull(existingEntity)){
                zxhjQqtjMapper.delByUuid(existingEntity.getUuid());
            }
            zxhjQqtjEntity.setUuid(TsBqUtils.getUUID());
            zxhjQqtjEntity.setSwjgmc(String.valueOf(row.getCell(1)));
            zxhjQqtjEntity.setYf(yf);
            zxhjQqtjEntity.setA1(getBigDecimal(row.getCell(1+1)));
            zxhjQqtjEntity.setA2(getBigDecimal(row.getCell(2+1)));
            zxhjQqtjEntity.setA3(getBigDecimal(row.getCell(3+1)));
            zxhjQqtjEntity.setA4(getBigDecimal(row.getCell(4+1)));
            zxhjQqtjEntity.setA5(getBigDecimal(row.getCell(5+1)));
            zxhjQqtjEntity.setA6(getBigDecimal(row.getCell(6+1)));
            zxhjQqtjEntity.setA7(getBigDecimal(row.getCell(7+1)));
            zxhjQqtjEntity.setA8(getBigDecimal(row.getCell(8+1)));
            zxhjQqtjEntity.setA9(getBigDecimal(row.getCell(9+1)));
            zxhjQqtjEntity.setA10(getBigDecimal(row.getCell(10+1)));
            zxhjQqtjEntity.setA11(getBigDecimal(row.getCell(11+1)));
            zxhjQqtjEntity.setA12(getBigDecimal(row.getCell(12+1)));
            zxhjQqtjEntity.setA13(getBigDecimal(row.getCell(13+1)));
            zxhjQqtjEntity.setA14(getBigDecimal(row.getCell(14+1)));
            zxhjQqtjEntity.setA15(getBigDecimal(row.getCell(15+1)));
            zxhjQqtjEntity.setA16(getBigDecimal(row.getCell(16+1)));
            zxhjQqtjEntity.setA17(getBigDecimal(row.getCell(17+1)));
            zxhjQqtjEntity.setA18(getBigDecimal(row.getCell(18+1)));
            zxhjQqtjEntity.setA19(getBigDecimal(row.getCell(19+1)));
            zxhjQqtjEntity.setA20(getBigDecimal(row.getCell(20+1)));
            zxhjQqtjEntity.setA21(getBigDecimal(row.getCell(21+1)));
            zxhjQqtjEntity.setA22(getBigDecimal(row.getCell(22+1)));
            zxhjQqtjEntity.setA23(getBigDecimal(row.getCell(23+1)));
            zxhjQqtjEntity.setA24(getBigDecimal(row.getCell(24+1)));
            zxhjQqtjEntity.setA25(getBigDecimal(row.getCell(25+1)));
            zxhjQqtjEntity.setA26(getBigDecimal(row.getCell(26+1)));
            zxhjQqtjEntity.setA27(getBigDecimal(row.getCell(27+1)));
            zxhjQqtjService.save(zxhjQqtjEntity);
            records.add(zxhjQqtjEntity);
            validDataCount++;
        }

        workbook.close();

        if (validDataCount == 0) {
            return ResponseEntity.ok(Result.error("Excel 中没有有效的数据行！"));
        }

        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("records", records);

        return ResponseEntity.ok(Result.success("共导入 " + validDataCount + " 条记录", dataMap));
    }

    // 执行台账-暴力虚开模板下载
    @GetMapping("/downloadZxhjBlxk_mb")
    public ResponseEntity<StreamingResponseBody> downloadZxhjBlxk_mb() {
        String filePath = "templates/excel/暴力虚开欠税统计表模板.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=暴力虚开欠税统计表模板.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }


    // 执行台账-清欠统计模板下载
    @GetMapping("/downloadZxhjQqtj_mb")
    public ResponseEntity<StreamingResponseBody> downloadZxhjQqtj_mb() {
        String filePath = "templates/excel/清欠统计表模板.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=清欠统计表模板.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }

    // 积案管理 - 模板下载
    @GetMapping("/downloadJagl_mb")
    public ResponseEntity<StreamingResponseBody> downloadJagl_mb() {
        String filePath = "templates/excel/积案管理模板.xlsx";
        InputStream inputStream = getClass().getClassLoader().getResourceAsStream(filePath);

        if (inputStream == null) {
            return ResponseEntity.notFound().build();
        }

        // 使用 StreamingResponseBody 实现流式传输
        StreamingResponseBody responseBody = outputStream -> {
            byte[] buffer = new byte[8192];
            int bytesRead;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                outputStream.write(buffer, 0, bytesRead);
                try {
                    TimeUnit.MILLISECONDS.sleep(10); // 模拟大文件延迟（可选）
                } catch (InterruptedException e) {
                    throw new RuntimeException(e);
                }
            }
            inputStream.close();
        };

        // 设置响应头
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
        headers.set("Content-Disposition", "attachment; filename=积案管理模板.xlsx");

        return ResponseEntity.ok()
                .headers(headers)
                .body(responseBody);
    }




    /**
     * 从 Excel 单元格安全获取 BigDecimal 值
     * @param cell Excel单元格
     * @param scale 小数位数 (可选，默认2位)
     * @param roundingMode 舍入模式 (可选，默认四舍五入)
     * @return BigDecimal 值，单元格为空或错误时返回null
     */
    public static BigDecimal getBigDecimal(Cell cell, Integer scale, RoundingMode roundingMode) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return null;
        }

        try {
            BigDecimal result;
            switch (cell.getCellType()) {
                case NUMERIC:
                    result = BigDecimal.valueOf(cell.getNumericCellValue());
                    break;
                case STRING:
                    String strValue = cell.getStringCellValue().trim();
                    if (strValue.isEmpty()) {
                        return null;
                    }
                    result = new BigDecimal(strValue);
                    break;
                case FORMULA:
                    switch (cell.getCachedFormulaResultType()) {
                        case NUMERIC:
                            result = BigDecimal.valueOf(cell.getNumericCellValue());
                            break;
                        case STRING:
                            String formulaStrValue = cell.getStringCellValue().trim();
                            if (formulaStrValue.isEmpty()) {
                                return null;
                            }
                            result = new BigDecimal(formulaStrValue);
                            break;
                        default:
                            return null;
                    }
                    break;
                default:
                    return null;
            }

            // 处理小数位数和舍入模式
            if (scale != null) {
                roundingMode = roundingMode != null ? roundingMode : RoundingMode.HALF_UP;
                result = result.setScale(scale, roundingMode);
            }
            return result;
        } catch (Exception e) {
            // 日志记录错误（实际使用时应该添加日志）
            // log.error("解析BigDecimal失败，单元格位置: {}, 错误: {}", cell.getAddress(), e.getMessage());
            return null;
        }
    }

    /**
     * 重载方法 - 默认小数位数和舍入模式
     */
    public static BigDecimal getBigDecimal(Cell cell) {
        return getBigDecimal(cell, null, null);
    }

    /**
     * 重载方法 - 指定小数位数，默认舍入模式
     */
    public static BigDecimal getBigDecimal(Cell cell, int scale) {
        return getBigDecimal(cell, scale, RoundingMode.HALF_UP);
    }


}
